import sqlite3
import json
from typing import List, Optional
from src.domain.emailqa import EmailQA, EmailType
from src.domain.repository import EmailQARepository
from src.infrastructure.filters import filter_string_fields_u200c


class SQLiteEmailQARepository(EmailQARepository):
    def __init__(self, db_path: str):
        self.db_path = db_path
    
    def _get_connection(self):
        return sqlite3.connect(self.db_path)
    
    def find_by_ids(self, ids: List[int]) -> List[EmailQA]:
        if not ids:
            return []
        
        placeholders = ','.join('?' for _ in ids)
        query = f"""
        SELECT id, system_conversation_id, q_body, q_subject, q_real_question, 
               q_question_subject, q_attachment_content, customer_service_answer,
               context_email_conversation_history_symmary, context_order_info
        FROM emailqa 
        WHERE id IN ({placeholders})
        """
        
        with self._get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(query, ids)
            rows = cursor.fetchall()
            
            email_qa_list = []
            for row in rows:
                # Convert row to dict and filter out \u200c characters
                row_dict = dict(row)
                filtered_row = filter_string_fields_u200c(row_dict)
                
                email_type = EmailType.PLATFORM if filtered_row['customer_service_answer'] and filtered_row['customer_service_answer'].startswith('Hi TEAM,') else EmailType.CUSTOMER
                
                context_order_info = filtered_row['context_order_info'] or '{}'
                try:
                    order_info_dict = json.loads(context_order_info)
                    if isinstance(order_info_dict, dict) and 'tracking_details' in order_info_dict:
                        tracking_details = order_info_dict.get('tracking_details', {})
                        if isinstance(tracking_details, dict) and 'trackings' in tracking_details:
                            del tracking_details['trackings']
                        context_order_info = json.dumps(order_info_dict)
                except json.JSONDecodeError:
                    pass
                
                email_qa = EmailQA(
                    id=filtered_row['id'],
                    system_conversation_id=filtered_row['system_conversation_id'] or '',
                    q_body=filtered_row['q_body'] or '',
                    q_subject=filtered_row['q_subject'] or '',
                    q_real_question=filtered_row['q_real_question'] or '',
                    q_question_subject=filtered_row['q_question_subject'] or '',
                    q_attachment_content=filtered_row['q_attachment_content'] or '',
                    customer_service_answer=filtered_row['customer_service_answer'] or '',
                    context_email_conversation_history_summary=filtered_row['context_email_conversation_history_symmary'] or '',
                    context_order_info=context_order_info,
                    email_type=email_type
                )
                email_qa_list.append(email_qa)
            
            return email_qa_list
    
    def get_by_id(self, id: int) -> Optional[EmailQA]:
        result = self.find_by_ids([id])
        return result[0] if result else None
    
    def create(self, email_qa: EmailQA) -> int:
        query = """
        INSERT INTO emailqa (
            system_conversation_id, q_body, q_subject, q_real_question, 
            q_question_subject, q_attachment_content, customer_service_answer,
            context_email_conversation_history, context_email_conversation_history_symmary, 
            context_order_info
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        
        with self._get_connection() as conn:
            cursor = conn.execute(query, (
                email_qa.system_conversation_id,
                email_qa.q_body,
                email_qa.q_subject,
                email_qa.q_real_question,
                email_qa.q_question_subject,
                email_qa.q_attachment_content,
                email_qa.customer_service_answer,
                email_qa.context_email_conversation_history_summary,
                email_qa.context_email_conversation_history_summary,
                email_qa.context_order_info
            ))
            conn.commit()
            return cursor.lastrowid